<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH W_PERMISSION AS
(SELECT T.*,
ROW_NUMBER() OVER(ORDER BY PROJECT_DICTIONARY.CODE, T_SITE.CODE, T.CODE) RN ,
T_SITE.CODE             AS SITE_CODE ,
T_SITE.NAME             AS SITE_NAME,
PROJECT_DICTIONARY.CODE AS PROJECT_CODE ,
PROJECT_DICTIONARY.NAME AS PROJECT_NAME
, type_DICTIONARY.code as type_Code
, type_DICTIONARY.name as type_Name
FROM C1_PERMISSION T
LEFT JOIN C1_SITE T_SITE
ON (T_SITE.ID = T.SITE_ID)
LEFT JOIN C1_DICTIONARY PROJECT_DICTIONARY
ON (PROJECT_DICTIONARY.ID = T_SITE.PROJECT_ID)
left join C1_DICTIONARY type_DICTIONARY on (type_DICTIONARY.id = t.type_id)
WHERE 1                = 1
<#if StringUtils.isNotBlank(siteId)>
AND T.SITE_ID          =:siteId
</#if>
<#if StringUtils.isNotBlank(typeId)>
AND T.TYPE_ID          = :typeId
</#if>
<#if StringUtils.isNotBlank(code)>
AND (instr(','||T.code||',', ','||:code||',') > 0 OR instr(','||T.name||',', ','||:code||',') > 0)
</#if>
)
SELECT T.*
FROM W_PERMISSION T
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>
